﻿using FirebirdSql.Data.FirebirdClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Linq;


namespace FileLoader
{
    public partial class LoaderForm : Form
    {
        FbConnection connection;
        string client = string.Empty;
        Dictionary<int, string> clients = new Dictionary<int, string>();
        Dictionary<int, string> treats = new Dictionary<int, string>();
        BackgroundWorker worker = new BackgroundWorker();
        BackgroundWorker initWorker = new BackgroundWorker();
        bool queueUpdate = false;
        Dictionary<int, int> treatDoc = new Dictionary<int, int>();
        List<string> files = new List<string>();
        Plexiglass opaqueForm;
        byte[] settingsPass = new byte[] { 130, 100, 56, 189, 237, 82, 190, 116, 131, 25, 4, 83, 121, 245, 30, 168 };
        string globalPass = string.Empty;
        System.Windows.Forms.Timer passTimer;
        

        public LoaderForm()
        {
            InitializeComponent();
        }

        private void LoaderForm_Load(object sender, EventArgs e)
        {
            CheckConfigPersistance();
            LoadSettings();
            UpdateConnectionSettings();
            initWorker.DoWork += initWorker_DoWork;
            initWorker.RunWorkerCompleted += initWorker_RunWorkerCompleted;
            worker.DoWork += UpdateDatasource;
            worker.RunWorkerCompleted += w_RunWorkerCompleted;
            opaqueForm = new Plexiglass(this);
            initWorker.RunWorkerAsync();
            
            PopulateLocalFiles();
            passTimer = new System.Windows.Forms.Timer() { Interval = 500 };
            passTimer.Tick += passTimer_Tick;
        }


        private void CheckConfigPersistance()
        {
            string cfgPath = Path.Combine(Application.StartupPath, "user.config");
            if (File.Exists(cfgPath))
            {
                string validCfgPath = Application.ExecutablePath + ".config";
                File.Move(cfgPath, validCfgPath);
                Configuration cfg = ConfigurationManager.OpenExeConfiguration(Application.ExecutablePath);
                // find section group
                ConfigurationSectionGroup group = cfg.SectionGroups[@"userSettings"];
                if (group == null) return;

                // find client section
                ClientSettingsSection clientSection = group.Sections["FileLoader.Properties.Settings"] as ClientSettingsSection;
                if (clientSection == null) return;

                // find setting element
                SettingElement settingElement = null;
                foreach (SettingElement s in clientSection.Settings)
                    Properties.Settings.Default[s.Name] = s.Value.ValueXml.InnerText;
                Properties.Settings.Default.Save();
                File.Delete(validCfgPath);
            }   
        }

        void passTimer_Tick(object sender, EventArgs e)
        {
            globalPass = string.Empty;
            passTimer.Stop();
        }

        private void LoadSettings()
        {
            LocalPath.Text = Properties.Settings.Default.LocalPath;
            NetworkPath.Text = Properties.Settings.Default.NetworkPath;
            User.Text = Properties.Settings.Default.User;
            Password.Text = PasswordUtil.Decrypt(Properties.Settings.Default.Password);
            DBPassword.Text = PasswordUtil.Decrypt(Properties.Settings.Default.DBPassword);
            DBUser.Text = Properties.Settings.Default.DBUser;
            DBAddress.Text = Properties.Settings.Default.DBAddress;
            DBPort.Text = Properties.Settings.Default.DBPort;
            DBPath.Text = Properties.Settings.Default.DBPath;
        }

        private void UpdateConnectionSettings()
        {
            if (connection != null && connection.State == ConnectionState.Open)
                connection.Close();
            string connectionString = String.Format(
            "User={0};" +
            "Password={1};" +
            "Database={2};" +
            "DataSource={3};" +
            "Port={4};ServerType=0;" 
            , DBUser.Text, DBPassword.Text, DBPath.Text, DBAddress.Text, DBPort.Text);
            try
            {
                connection = new FbConnection(connectionString);
            }
            catch
            {
                return;
            }
        }

        void initWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            OpenConnection();
        }

        void OpenConnection()
        {
            try
            {
                connection.Open();
            }
            catch
            {
                MessageBox.Show("Не удалось подключиться к базе данных");
            }
        }

        private void PopulateDoctors()
        {
            FbTransaction t = InitTransaction();
            FbCommand c = new FbCommand(
                "select  d.DCode, d.FullName from doctor d left join departments dp on dp.DepNum = d.DepNum " +
            "WHERE d.DCode > 0 and d.ViewInSched = 1 and dp.DepCode is not null ORDER BY d.FullName",
                connection, t);
            FbDataReader r = c.ExecuteReader();
            Dictionary<string, string> doctors = new Dictionary<string, string>();
            while (r.Read())
            {
                doctors.Add(r.GetValue(0).ToString(), r.GetValue(1).ToString());
                r.NextResult();
            }
            cbxDoctor.SelectedIndexChanged -= cbxDoctor_SelectedIndexChanged;
            cbxDoctor.ValueMember = "Key";
            cbxDoctor.DisplayMember = "Value";
            cbxDoctor.DataSource = new BindingSource(doctors, null);
            cbxDoctor.SelectedIndex = -1;
            cbxDoctor.SelectedIndexChanged += cbxDoctor_SelectedIndexChanged;

            t.Commit();
        }

        private void tbxPatient_TextChanged(object sender, EventArgs e)
        {
            if (tbxPatient.Text.Length > 2)
            {

                if (worker.IsBusy)
                {
                    queueUpdate = true;
                    return;
                }
                client = tbxPatient.Text;
                worker.RunWorkerAsync();
            }
        }

        void initWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            opaqueForm.Close();
            RefreshDoctors();
        }

        void RefreshDoctors()
        {
            cbxDoctor.DataSource = null;
            if (connection == null || connection.State != ConnectionState.Open) return;
            PopulateDoctors();
            try
            {
                cbxDoctor.SelectedValue = Properties.Settings.Default.Doctor;
            }
            catch
            {
                cbxDoctor.SelectedIndex = -1;
                Properties.Settings.Default["Doctor"] = string.Empty;
            }
            if (cbxDoctor.SelectedIndex == -1)
                tabControl1.SelectedIndex = 1;
        }

        void w_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

            if (clients.Count == 0)
            {
                lbxPatients.DataSource = null;
                lbxPatients.Hide();
            }
            else
            {
                lbxPatients.SelectionMode = SelectionMode.None;
                BindClients(clients);
                lbxPatients.SelectionMode = SelectionMode.One;
                lbxPatients.SelectedIndex = -1;
                ShowPatients();
            }
            
          
            if (queueUpdate)
            {
                queueUpdate = false;
                client = tbxPatient.Text;
                worker.RunWorkerAsync();
            }
        }

        private void BindClients(object source)
        {
            lbxPatients.DataSource = new BindingSource(source, null);
            lbxPatients.ValueMember = "Key";
            lbxPatients.DisplayMember = "Value";
        }

        private void UpdateDatasource(object sender, EventArgs e)
        {
            
            Thread wrap = new Thread(delegate()
            {
                using (FbTransaction t = InitTransaction())
                {
                    FbCommand c = new FbCommand(
                        String.Format(
                        "SELECT FIRST 100 PCODE, FULLNAME, BDATE, HISTNUM FROM CLIENTS WHERE lower(FULLNAME) LIKE '%{0}%' " +
                        "ORDER BY CASE WHEN lower(FULLNAME) like '{0} %' THEN 0 WHEN lower(FULLNAME) like '{0}%' THEN 1 WHEN lower(FULLNAME) like '% {0}%' THEN 2 ELSE 3 END, FULLNAME;",
                        client.ToLower()),
                        connection, t);
                    FbDataReader r = c.ExecuteReader();
                    clients.Clear();
                    while (r.Read())
                    {
                        string date = r.IsDBNull(2) ? "<д.р. не указана>" : ((DateTime)r.GetValue(2)).ToString("dd.MM.yyyy");
                        string name = String.Format("{0}; {1}; Карта: {2}", r.GetValue(1).ToString(), date, r.GetValue(3).ToString());
                        clients.Add(Convert.ToInt32(r.GetValue(0)), name);
                        r.NextResult();
                    }
                    t.Commit();
                }
            });
            wrap.Start();
            if (!wrap.Join(TimeSpan.FromSeconds(5)))
            {
                wrap.Abort();
                connection.Close();
                OpenConnection();
            }            
        }

       

        void UpdateTreats()
        {
            cbxTreat.DataSource = null;
            treats.Clear();
            treatDoc.Clear();
            if (cbxDoctor.SelectedIndex == -1)
            {
                tabControl1.SelectedIndex = 1;
                MessageBox.Show("Укажите врача");
                return;
            }
            if (lbxPatients.SelectedIndex != -1)
            {
                FbTransaction t = InitTransaction();
                FbCommand c = new FbCommand("SELECT t.TREATCODE, a.DNAME, t.TREATDATE, t.DCODE " +
                "FROM TREAT t " +
                "left join DOCTOR a on a.DCODE = t.DCODE " +
                "where pcode = '" + lbxPatients.SelectedValue + "' and t.DCODE = '" + cbxDoctor.SelectedValue + "' " +
                "ORDER BY t.TREATDATE DESC;", connection, t);
                FbDataReader r = c.ExecuteReader();

                while (r.Read())
                {
                    int treatId = (Convert.ToInt32(r.GetValue(0)));
                    string date = r.IsDBNull(2) ? "<д.р. не указана>" : ((DateTime)r.GetValue(2)).ToString("dd.MM.yyyy");
                    string name = String.Format("{1} - {0}", date, r.GetValue(1).ToString());
                    treats.Add(treatId, name);
                    treatDoc.Add(treatId, (Convert.ToInt32(r.GetValue(3))));
                    r.NextResult();
                }

                t.Commit();
                if (treats.Count == 0) cbxTreat.DataSource = null;
                else
                {
                    cbxTreat.SelectedIndexChanged -= cbxTreat_SelectedIndexChanged;
                    cbxTreat.DataSource = new BindingSource(treats, null);
                    cbxTreat.ValueMember = "Key";
                    cbxTreat.DisplayMember = "Value";
                    cbxTreat.SelectedIndex = -1;
                    cbxTreat.SelectedIndexChanged += cbxTreat_SelectedIndexChanged;
                }
            }
        }

        private void cbxTreat_SelectedIndexChanged(object sender, EventArgs e)
        {
            grvTreatFiles.Rows.Clear();
            if (cbxTreat.SelectedIndex == -1) return;
            PopulateRemoteFiles();
        }

        private void PopulateRemoteFiles()
        {
            grvTreatFiles.Rows.Clear();
            FbTransaction t = InitTransaction();
            FbCommand c = new FbCommand("SELECT PHOTOID, FULLPATH FROM PHOTO " +
            "where TREATCODE = '" + cbxTreat.SelectedValue + "';", connection, t);
            FbDataReader r = c.ExecuteReader();
            while (r.Read())
            {
                string fileName = r.GetValue(1).ToString();
                string[] nameParts = fileName.Split(new char[] { '-' }, 3);
                if (nameParts.Length > 2)
                    grvTreatFiles.Rows.Add(nameParts[2]);
                else
                    grvTreatFiles.Rows.Add(fileName);
            }

            t.Commit();
            grvTreatFiles.ClearSelection();
        }

        private void CommitLoad()
        {
            int fCount = files.Count;
            for (int i = fCount - 1; i >= 0; i--)
            {
                if (grvLocalFiles.Rows[i].Selected)
                    File.Delete(files[i]);
            }
            PopulateLocalFiles();
            PopulateRemoteFiles();
        }

        private FbTransaction InitTransaction()
        {
            if (connection.State != ConnectionState.Open)
                OpenConnection();

            return connection.BeginTransaction();
        }

        private void settingChanged(object sender, EventArgs e)
        {
            if (sender == Password || sender == DBPassword)
                Properties.Settings.Default[(sender as TextBox).Name] = PasswordUtil.Encrypt((sender as TextBox).Text);
            else Properties.Settings.Default[(sender as TextBox).Name] = (sender as TextBox).Text;
            
            Properties.Settings.Default.Save();
            
            if (sender == LocalPath)
            {
                setWatcher();
                PopulateLocalFiles();
            }
        }

        private void setWatcher()
        {
            FileSystemWatcher watch = new FileSystemWatcher();
            watch.Path = LocalPath.Text;

            watch.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite | NotifyFilters.FileName | NotifyFilters.DirectoryName;


            watch.Created += watch_Created;
            watch.Deleted += watch_Deleted;
            watch.Renamed += watch_Renamed;

            watch.EnableRaisingEvents = true;

            Console.ReadLine();
        }

        void watch_Renamed(object sender, RenamedEventArgs e)
        {
            int i = files.IndexOf(e.OldFullPath);
            if (i >= 0)
                files[i] = e.FullPath;
        }

        void watch_Deleted(object sender, FileSystemEventArgs e)
        {
            for (int i = files.Count - 1; i >= 0; i--)
                if (files[i] == e.FullPath)
                {
                    files.RemoveAt(i);
                    this.Invoke((MethodInvoker)delegate
                    {
                        if (grvLocalFiles.Rows.Count > i)
                            grvLocalFiles.Rows.Remove(grvLocalFiles.Rows[i]);
                    });

                    return;
                }
        }

        void watch_Created(object sender, FileSystemEventArgs e)
        {
            files.Add(e.FullPath);
            this.Invoke((MethodInvoker)delegate
            {
                grvLocalFiles.Rows.Add(Path.GetFileNameWithoutExtension(e.Name));
            });
        }

        private void PopulateLocalFiles()
        {
            grvLocalFiles.Rows.Clear();
            files.Clear();
            if (Directory.Exists(LocalPath.Text))
            {
                foreach (var f in Directory.GetFiles(LocalPath.Text))
                {
                    files.Add(f);
                    grvLocalFiles.Rows.Add(Path.GetFileNameWithoutExtension(f));
                }
            }
            else
                grvLocalFiles.Rows.Add("Нет доступа к локальной папке!");

            grvLocalFiles.ClearSelection();
        }

        private void ChangeFolder(object sender, EventArgs e)
        {
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (sender == btnChangeLocal)
                    LocalPath.Text = fbd.SelectedPath;
                else
                    NetworkPath.Text = fbd.SelectedPath;
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {

            if (cbxTreat.SelectedIndex == -1)
            {
                MessageBox.Show("Пожалуйста, выберите пациента и прием");
                return;
            }
            if (!Directory.Exists(LocalPath.Text))
            {
                MessageBox.Show("Неверно выбрана папка с локальными файлами");
                return;
            }
            if (files.Count == 0)
            {
                MessageBox.Show("Нечего выгружать!");
                return;
            }
            if (grvLocalFiles.SelectedRows.Count == 0)
            {
                MessageBox.Show("Выберите файлы для выгрузки");
                return;
            }

            try
            {
                using (new NetworkConnection(NetworkPath.Text, new System.Net.NetworkCredential(User.Text, Password.Text)))
                {

                    string query = "EXECUTE BLOCK AS BEGIN\n ";

                    FbTransaction t = InitTransaction();
                    FbCommand c = new FbCommand("SELECT MAX(PHOTOID) FROM PHOTO;", connection, t);
                    int id = Convert.ToInt32(c.ExecuteScalar());
                    id++;
                    for (int i = 0; i < files.Count; i++)
                    {
                        if (!grvLocalFiles.Rows[i].Selected) continue;
                        // Путь к исходному файлу в папке
                        string srcFileName = files[i];
                        // Имя файла, заданное пользователем
                        string userFileName = grvLocalFiles.Rows[i].Cells[0].Value.ToString();
                        // Конечное имя файла по шаблону
                        string destFileName = String.Format("{0}-{1}-{2}{3}", lbxPatients.SelectedValue, cbxTreat.SelectedValue, userFileName, Path.GetExtension(srcFileName));
                        string destPath = Path.Combine(NetworkPath.Text, destFileName);
                        FileInfo fi = new FileInfo(srcFileName);
                        if (File.Exists(destPath))
                        {
                            if (MessageBox.Show("Файл " + userFileName + " уже существует.\nЗаменить и продолжить или отменить загрузку?", "Внимание", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.Cancel)
                            {
                                t.Commit();
                                return;
                            }
                            else
                                query += "DELETE FROM PHOTO WHERE FULLPATH = '" + destPath + "'; ";
                        }
                        File.Copy(srcFileName, destPath, true);

                        string fileHash = string.Empty;
                        using (FileStream stream = File.OpenRead(srcFileName))
                        {
                            MD5 md5 = MD5.Create();
                            BitConverter.ToString(md5.ComputeHash(stream)).Replace("-", "");
                        }

                        query +=
                            String.Format("INSERT INTO PHOTO (PHOTOID, PCODE, TOOTHCODE, \"COMMENT\", FILIAL, FULLPATH, PHOTOTYPE, DCODE, WRKDATE, CODEPARAMS, TREATCODE, FILEDATE, FILEHASH) VALUES ('{0}', '{1}', NULL, NULL, '1', '{2}', '10', '{3}', '{4}', '10003899', '{5}', '{6}', '{7}');",
                            id++,
                            lbxPatients.SelectedValue,
                            destPath,
                            treatDoc[Convert.ToInt32(cbxTreat.SelectedValue)].ToString(),
                            DateTime.Now.ToString("dd.MM.yyyy, 00:00:00.000"),
                            cbxTreat.SelectedValue,
                            fi.CreationTime.ToString("dd.MM.yyyy, HH:mm:ss.000"),
                            fileHash);
                    }
                    query += "END";
                    c = new FbCommand(query, connection, t);
                    c.ExecuteNonQuery();
                    t.Commit();
                }
                MessageBox.Show("Файлы успешно выгружены!");
                CommitLoad();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при загрузке: " + ex.Message);
                connection.Close();
                OpenConnection();
            }
        }


        private void cbxDoctor_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbxDoctor.SelectedIndex != -1)
            {
                Properties.Settings.Default["Doctor"] = cbxDoctor.SelectedValue.ToString();
                Properties.Settings.Default.Save();

                if (lbxPatients.SelectedIndex != -1)
                    UpdateTreats();
            }
        }



        private void grvLocalFiles_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            if (e.RowIndex == -1) return;
            foreach (DataGridViewRow r in grvLocalFiles.Rows)
                if (e.RowIndex != r.Index && e.FormattedValue.ToString() == r.Cells[0].Value.ToString())
                {
                    e.Cancel = true;
                    fileError.Visible = true;
                    return;
                }
            fileError.Visible = false;
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            opaqueForm = new Plexiglass(this);
            UpdateConnectionSettings();
            OpenConnection();
            if (connection.State == ConnectionState.Open)
                MessageBox.Show("Соединение установлено");
            opaqueForm.Close();
            RefreshDoctors();
        }

        private void LoaderForm_KeyPress(object sender, KeyPressEventArgs e)
        {
            globalPass += e.KeyChar;
            if (globalPass.Length == 6 && CheckPass(MD5.Create().ComputeHash(Encoding.UTF8.GetBytes(globalPass))))
            {
                gbxDatabase.Visible = gbxNetwork.Visible = btnHide.Visible = true;
            }
            else
            {
                if (passTimer.Enabled) passTimer.Stop();
                passTimer.Start();
            }
        }

        private void btnHide_Click(object sender, EventArgs e)
        {
            gbxNetwork.Visible = gbxDatabase.Visible = btnHide.Visible = false;
        }

        private bool CheckPass(byte[] p)
        {
            for (int i = 0; i < p.Length; i++)
                if (p[i] != settingsPass[i]) return false;
            return true;
        }

        private void lbxPatients_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            tbxPatient.TextChanged -= tbxPatient_TextChanged;
            tbxPatient.Text = lbxPatients.Text;
            BindClients(lbxPatients.SelectedItem);
            tbxPatient.TextChanged += tbxPatient_TextChanged;
            UpdateTreats();
            lbxPatients.Hide();
        }

        private void tbxPatient_Enter(object sender, EventArgs e)
        {
            ShowPatients();
        }

        private void ShowPatients()
        {
            if (lbxPatients.Items.Count > 0 && !lbxPatients.Visible)
            {
                lbxPatients.Show();
            }
            lbxPatients.Height = (Math.Min(lbxPatients.Items.Count, 10) + 1) * lbxPatients.ItemHeight;
        }

        private void tbxPatient_Leave(object sender, EventArgs e)
        {
            if (!lbxPatients.Focused)
            lbxPatients.Hide();
        }

        private void tbxPatient_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Down)
                ShowPatients();
        }

    }
}
